package application.Service;


import application.Pojo.Failure;
import application.Pojo.HistoryRecord;
import application.utils.Utils;
import com.alibaba.fastjson.JSONObject;


import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * @Auther: Ohto_Ai
 * @Date: 2022/2/22 - 02 - 22 - 20:19
 * @Description: Service
 * @version: 1.0
 */
@WebServlet(urlPatterns = "/getrecord")
public class GetRecord extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");

        //用户名（名字+学号）
        String username = null;

        //书籍id
        String bookid = null;

        //书籍名
        String bookname = null;

        //借书时间
        String borrow_date= null;

        //还书时间
        String return_date= null;

        //书籍信息数据库的连接、处理、结果对象
        Connection record = null;
        Statement stmt = null;
        ResultSet rs = null;

        //书籍信息数据库的连接、处理、结果对象
        Connection bookinformation = null;
        Statement istmt = null;
        ResultSet irs = null;

        try {
            //创建工具对象
            Utils utils = new Utils();
            //获取前端usernameJSON数据
            JSONObject jsonObject = utils.getJSONObject(req);
            username = jsonObject.getString("username");

            //获取连接对象
            record =  utils.JDBCConection("com.mysql.jdbc.Driver","jdbc:mysql://127.0.0.1:3306/apply","root","123456");
            bookinformation = utils.JDBCConection("com.mysql.jdbc.Driver","jdbc:mysql://127.0.0.1:3306/apply","root","123456");
            //设置事务不自动提交
            record.setAutoCommit(false);

            //创建相应sql语句
            StringBuilder sbsql = new StringBuilder("SELECT DISTINCT username,bookid,borrow_date,return_date  FROM borrowapply WHERE username =\"");
            sbsql.append(username);
            sbsql.append("\"ORDER BY borrow_date ;");
            String sql = sbsql.toString();

            //执行sql语句
            stmt = record.createStatement();
            rs = stmt.executeQuery(sql);

            //创建集合存储从数据库获取的历史记录
            ArrayList<HistoryRecord> records = new ArrayList<>();

            while(rs.next()){

                //获取数据库信息
                bookid = rs.getString("bookid");

                StringBuilder sbisql = new StringBuilder("SELECT id,bookname FROM bookinformation WHERE id = \"");
                sbisql.append(bookid);
                sbisql.append("\";");

                istmt = bookinformation.createStatement();
                irs = istmt.executeQuery(sbisql.toString());
                while(irs.next()){
                    bookname =  irs.getString("bookname");
                }
                String isql = sbisql.toString();
                username = rs.getString("username");
                borrow_date = rs.getString("borrow_date").substring(0,10);
                return_date = rs.getString("return_date").substring(0,10);




                //创建临时对象存储历史记录
                HistoryRecord TempRecord = new HistoryRecord();
                TempRecord.setUsername(username);
                TempRecord.setBookname(bookname);
                TempRecord.setBorrow_date(borrow_date);
                TempRecord.setReturn_time(return_date);

                //将临时对象的信息存储入集合中
                records.add(TempRecord);

            }

            //将集合信息转换成JSON格式字符串并获取
            String data = JSONObject.toJSONString(records);

            if (!data.equals("[]")) {
                //把json数据返回给浏览器：
                resp.getWriter().write(data);
            } else {
                Failure failure = new Failure();
                failure.setCode(String.valueOf("-1"));
                failure.setMessage("警告！返回错误！");

                String json = JSONObject.toJSONString(failure);
                resp.getWriter().write(json);
            }

        }catch(SQLException se){

            se.printStackTrace();
            //捕捉异常并且 返回包含错误码信息对象的JSON字符串
            Failure failure = new Failure();
            failure.setCode(String.valueOf(se.getErrorCode()));
            failure.setMessage(se.getMessage());

            String json = JSONObject.toJSONString(failure);

            //将json对象转化为字符串在终端输出
            System.out.print(json);

            //把json数据返回给浏览器：
            resp.getWriter().write(json);

        }catch(Exception e) {

            e.printStackTrace();
            //捕捉异常并且 返回包含错误码信息对象的JSON字符串
            Failure failure = new Failure();
            failure.setCode(String.valueOf(-1));
            failure.setMessage(e.getMessage());

            String json = JSONObject.toJSONString(failure);

            //将json对象转化为字符串在终端输出
            System.out.print(json);

            //把json数据返回给浏览器：
            resp.getWriter().write(json);
            //出现异常事务回滚
            try {
               record.rollback();
            } catch (SQLException ex) {

                ex.printStackTrace();
                //捕捉异常并且 返回包含错误码信息对象的JSON字符串
                Failure rollbackfailure = new Failure();
                failure.setCode(String.valueOf(ex.getErrorCode()));
                failure.setMessage(ex.getMessage());

                String rollbackjson = JSONObject.toJSONString(failure);

                //将json对象转化为字符串在终端输出
                System.out.print(rollbackjson);

                //把json数据返回给浏览器：
                resp.getWriter().write(rollbackjson);

            }
        }finally {
            try {
                //关闭资源
                if ( rs != null){
                    rs.close();
                }
                if (stmt != null){
                    stmt.close();
                }
                if (record != null){
                    record.close();
                }

            } catch (SQLException e) {

                e.printStackTrace();
                //捕捉异常并且 返回包含错误码信息对象的JSON字符串
                Failure failure = new Failure();
                failure.setCode(String.valueOf(e.getErrorCode()));
                failure.setMessage(e.getMessage());

                String json = JSONObject.toJSONString(failure);

                //将json对象转化为字符串在终端输出
                System.out.print(json);

                //把json数据返回给浏览器：
                resp.getWriter().write(json);
                //出现异常事务回滚
                try {
                    record.rollback();
                } catch (SQLException ex) {

                    ex.printStackTrace();
                    //捕捉异常并且 返回包含错误码信息对象的JSON字符串
                    Failure rollbackfailure = new Failure();
                    failure.setCode(String.valueOf(ex.getErrorCode()));
                    failure.setMessage(ex.getMessage());

                    String rollbackjson = JSONObject.toJSONString(failure);

                    //将json对象转化为字符串在终端输出
                    System.out.print(rollbackjson);

                    //把json数据返回给浏览器：
                    resp.getWriter().write(rollbackjson);
                }
            }
        }
    }
}

